package forum.dao;

import forum.model.Section;
import forum.model.Topic;
import forum.model.User;
import forum.util.DBUtil;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author haomin
 * @date 2022/06/30 16:38
 **/
public class TopicDao {

    // 查询板块帖子数
    @SneakyThrows
    public int sTopicCount(Integer sID) {
        String sql = "select count(*) from topic where sID = ?";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,sID);
                try (ResultSet rs = ps.executeQuery()){
                    rs.next();
                    int count = rs.getInt("count(*)");

                    updateSTopicCount(sID, count);
                    return count;
                }
            }
        }
    }

    // 更新板块帖子数
    @SneakyThrows
    public void updateSTopicCount(int sID, int count) {
        String sql = "update section set sTopicCount = ? where sID = ?";
        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1,count);
                ps.setInt(2,sID);

                ps.executeUpdate();
            }
        }
    }

    // 得到主贴集合BySid
    @SneakyThrows
    public List<Topic> selectListByUid(int sid){
        List<Topic> list = new ArrayList<>();
        String sql = "select tID, uID, tTopic, tContent, tTime, tReplyCount, tClickCount from topic where sID = ? order by tTime desc";
        try(Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,sid);
                System.out.println("DEBUG: " + ps);
                try (ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        Topic topic = new Topic();
                        topic.tID = rs.getInt("tID");
                        topic.sID = sid;
                        topic.uID = rs.getInt("uID");
                        topic.username = new UserDao().selectUserNameByUid(topic.uID);
                        topic.tTopic = rs.getString("tTopic");
                        topic.tContent = rs.getString("tContent");
                        String Time = rs.getString("tTime");
                        topic.tTime = Time.substring(0,Time.length()-5);

                        topic.tReplyCount = new ReplyDao().tReplyCount(topic.tID);
                        topic.tClickCount = rs.getInt("tClickCount");

                        list.add(topic);
                    }
                }
            }
        }
        return list;
    }

    // 得到主贴集合ByKeyword
    @SneakyThrows
    public List<Topic> selectTopicListByKeyword(String keyword) {
        List<Topic> list = new ArrayList<>();
        String sql = "select * from topic where tTopic like ?;";

        System.out.println(sql);
        try (Connection c = DBUtil.connection()) {
            System.out.println(sql);

            try (PreparedStatement ps = c.prepareStatement(sql)){
                System.out.println(sql);
                String key = "%"+keyword+"%";
                ps.setString(1,key);
                System.out.println(sql);

                try (ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        Topic topic = new Topic();
                        topic.tID = rs.getInt("tID");
                        topic.sID = rs.getInt("sId");
                        topic.uID = rs.getInt("uID");
                        topic.username = new UserDao().selectUserNameByUid(topic.uID);
                        topic.tTopic = rs.getString("tTopic");
                        topic.tContent = rs.getString("tContent");
                        String Time = rs.getString("tTime");
                        topic.tTime = Time.substring(0,Time.length()-5);

                        topic.tReplyCount = new ReplyDao().tReplyCount(topic.tID);
                        topic.tClickCount = rs.getInt("tClickCount");

                        list.add(topic);
                    }
                }
            }
        }

        return list;
    }

    // 查询一个主贴ByTid
    @SneakyThrows
    public Topic selectTopicByTid(int tid) {
        String sql = "select sID, uID, tTopic, tContent, tTime, tReplyCount, tClickCount from topic where tID = ?";
        try(Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,tid);

                System.out.println("DEBUG: " + ps);
                try (ResultSet rs = ps.executeQuery()){
                    rs.next();

                    Topic topic = new Topic();
                    topic.tID = tid;
                    topic.sID = rs.getInt("sID");
                    topic.uID = rs.getInt("uID");
                    topic.username = new UserDao().selectUserNameByUid(topic.uID);
                    topic.tTopic = rs.getString("tTopic");
                    topic.tContent = rs.getString("tContent");
                    String Time = rs.getString("tTime");
                    topic.tTime = Time.substring(0,Time.length()-5);

                    topic.tReplyCount = new ReplyDao().tReplyCount(topic.tID);
                    topic.tClickCount = rs.getInt("tClickCount");

                    return topic;
                }
            }
        }
    }

    // 插入主贴
    @SneakyThrows
    public void insert(int sid, Integer uID, String title, String content) {
        String sql = "insert into topic (sID, uID, tTopic, tContent) values (?, ?, ?, ?)";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,sid);
                ps.setInt(2,uID);
                ps.setString(3,title);
                ps.setString(4,content);

                ps.executeUpdate();
                System.out.println("插入完成");
            }
        }
    }

    // 更新主贴浏览次数
    @SneakyThrows
    public void update(int tid) {
        String sql = "update topic set tClickCount = tClickCount+1 where tid = ?";
        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1,tid);

                ps.executeUpdate();
            }
        }
    }


}